package com.weng.coder.service;

import cn.hutool.db.ds.simple.SimpleDataSource;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.expr.SQLVariantRefExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlPrimaryKey;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlCreateTableStatement;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.weng.bigdata.core.jdbc.model.meta.ColumnMeta;
import com.weng.bigdata.framework.jdbc.utils.JdbcUtil;
import com.weng.coder.common.enums.SqlOpTypeEnums;
import com.weng.coder.common.utils.Constants;
import com.weng.coder.common.utils.DBType;
import com.weng.coder.common.utils.DbUtils;
import com.weng.coder.common.utils.NameConvertUtils;
import com.weng.coder.entity.CodeTable;
import com.weng.coder.entity.CoderCondition;
import com.weng.coder.entity.Field;
import com.weng.coder.entity.Project;
import com.weng.framework.core.exception.DataSourceException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 通过SQL建库脚本生成表配置
 *
 * @author wengzhonghui
 */
@Service
@Slf4j
public class GetTableAndFieldsBySqlServiceImpl implements GetTableAndFieldsBySqlService {

    /**
     * 通过建表SQL获取多个表对象
     *
     * @param sql 建表SQL
     * @return
     */
    @Override
    public List<CodeTable> getTableListBySql(Project project, String sql) {
        final String dbType = Constants.getDefaultDbType();
        List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
        return getCodeTableListBySQLStatement(project, stmtList, sql);
    }

    private List<CodeTable> getCodeTableListBySQLStatement(Project project,List<SQLStatement> stmtList, String sql)  {
        List<CodeTable> tableList = new ArrayList<>();
        for (SQLStatement sqlStatement : stmtList) {
            CodeTable table = getCodeTableBySQLStatement(project, sqlStatement, sql);
            if (table != null) {
                table.setSqlContent(sql);
                tableList.add(table);
            }
        }
        return tableList;
    }

    public CodeTable getCodeTableBySQLStatement(Project project,SQLStatement sqlStatement, String sql) {
        if (sqlStatement instanceof MySqlCreateTableStatement) {
            MySqlCreateTableStatement mySqlCreateTableStatement = (MySqlCreateTableStatement) sqlStatement;
            String tableRemarks = getTableRemarksOfMysql(mySqlCreateTableStatement);
            String tableName = getTableNameOfMysql(mySqlCreateTableStatement);
            tableRemarks = StringUtils.isEmpty(tableRemarks) ? tableName : tableRemarks;
            CodeTable table = new CodeTable();
            table.setCode(tableName);
            table.setRemarks(tableRemarks);
            List<Field> fields = getFieldList(mySqlCreateTableStatement);
            table.setFieldList(fields);
            table.setSqlType(SqlOpTypeEnums.CREATE.getValue());
            return table;
        } else if (sqlStatement instanceof SQLSelectStatement) {
            // 处理查询语句的场景
            SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) sqlStatement;
            List<Field> fields = null;
            if(project== null || StringUtils.isEmpty(project.getDbUrl()) || StringUtils.isEmpty(project.getDbUsername()) ){
                // 没有配置数据源的时候，
                fields = getFieldListBySelect(sqlSelectStatement);
            }else {
                // 有数据源的情况,可以到数据中获取对应字段的注释
                try {
                    fields = getFieldListBySelectSqlWithComment(sql, project.getDbUrl(), project.getDbUsername(), project.getDbPassword());
                } catch (SQLException e) {
                    log.error(e.getMessage(), e);
                    fields = getFieldListBySelect(sqlSelectStatement);
                }
            }

            // 获取查询条件中的查询条件
            List<CoderCondition> coderConditions = getWhereCondition(sqlSelectStatement);

            // 获取查询语句中其中一个表名,用于生成类
            String tableName = "detaultTbName";
            MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
            sqlStatement.accept(visitor);
            Map<TableStat.Name, TableStat> tableStatMap = visitor.getTables();
            for (Map.Entry<TableStat.Name, TableStat> tableStatEntry : tableStatMap.entrySet()) {
                tableName = tableStatEntry.getKey().getName();
            }
            // 查询语句，表名尽量不重复
            tableName = DbUtils.clearSpecialCharacters(tableName) + "_" +System.currentTimeMillis();
            CodeTable table = new CodeTable();
            table.setSqlType(SqlOpTypeEnums.SELECT.getValue());
            table.setCode(tableName);
            table.setFieldList(fields);
            table.setConditionList(coderConditions);
            return table;

        }
        return null;
    }


    /**
     * 获取查询语句中的属性列表
     *
     * @param sqlSelectStatement
     * @return
     */
    private List<Field> getFieldListBySelect(SQLSelectStatement sqlSelectStatement) {
        List<Field> fields = new ArrayList<>();
        List<SQLSelectItem> list = (List<SQLSelectItem>) sqlSelectStatement.getSelect().getQueryBlock().getSelectList();
        for (SQLSelectItem sqlSelectItem : list) {
            String column = "";
            if (sqlSelectItem.getAlias() != null) {
                column = sqlSelectItem.getAlias();
            } else {
                column = sqlSelectItem.getExpr().toString();
            }
            if (column.indexOf(".*") >= 0) {
                continue;
            }
            // 处理d.author_username这种场景，只返回author_username
            if (column.indexOf(".") >= 0) {
                column = column.substring(column.indexOf(".") + 1, column.length());
            }
            fields.add(getDefaultField(column));
        }
        // 处理select * 的特殊场景,没有字段，则加一个默认的字段
        if (fields.size() == 0) {
            fields.add(getDefaultField("no_column"));
        }
        return fields;
    }

    /**
     * 通过SQL获取查询SQL各个字段的元数据,同时带上字段注释
     * @param sql
     * @param jdbcUrl
     * @param username
     * @param password
     * @return
     * @throws SQLException
     */
    public  List<Field> getFieldListBySelectSqlWithComment(String sql, String jdbcUrl, String username, String password) throws SQLException {

        List<Field> fieldList = new ArrayList<>();
        List<ColumnMeta> list = JdbcUtil.getMetaDataBySqlWithComment(sql, jdbcUrl, username, password);
        if(list != null){
            list.stream().forEach(columnMeta -> {
                Field field = new Field();
                field.setName(columnMeta.getName());
                field.setColumn(columnMeta.getName());
                String jdbcType =  DbUtils.clearSpecialCharacters(columnMeta.getJdbcType());
                if(jdbcType.indexOf("(")>0){
                    jdbcType = jdbcType.substring(0, jdbcType.indexOf("("));
                }
                field.setJdbcType(jdbcType);
                field.setIsPrimary(Constants.FLAG_FALSE);
                field.setJavaType(getJavaTypeByJdbcType(jdbcType));
                field.setRemarks(columnMeta.getComment());
                fieldList.add(field);
            });
        }
        return fieldList;
    }


    /**
     * 获取数据源
     *
     * @param jdbcUrl
     * @param userName
     * @param password
     * @return
     * @throws DataSourceException
     */
    private static DataSource getDataSource(String jdbcUrl, String userName, String password) throws DataSourceException {
        jdbcUrl = setJdbcOfRemarkProperties(jdbcUrl);
        return  new SimpleDataSource(jdbcUrl, userName, password);
    }

    /**
     *  要获取remark信息，还需要加上额外属性支持，如jdbc:mysql://x.x.x.x:3306/djypt_test?remarks=true&useInformationSchema=true
     * @param jdbcUrl
     * @return
     */
    private static String setJdbcOfRemarkProperties(String jdbcUrl){
        StringBuilder jdbcUrlSb = new StringBuilder(jdbcUrl.trim());
        String remarkProperty = "remarks=true&useInformationSchema=true";
        if (jdbcUrlSb.indexOf("?") < 0) {
            jdbcUrlSb.append("?");
        }
        if (jdbcUrlSb.indexOf("&") > 0) {
            jdbcUrlSb.append("&");
        }
        jdbcUrlSb.append(remarkProperty);
        return jdbcUrlSb.toString();
    }

    /**
     * 获取查询条件中，需要查询的属性(也就是查询条件中带?号的，例如where aa=?中的aa)
     *
     * @param sqlSelectStatement
     * @return
     */
    private List<CoderCondition> getWhereCondition(SQLSelectStatement sqlSelectStatement) {
        List<CoderCondition> coderConditions = new ArrayList<>();
        SQLBinaryOpExpr whereExpr = (SQLBinaryOpExpr) sqlSelectStatement.getSelect().getQueryBlock().getWhere();
        getWhereExpr(whereExpr, coderConditions);
        return coderConditions;
    }

    /**
     * 获取带条件中带?号的属性
     *
     * @param whereExpr, whereColums
     * @return
     */
    public void getWhereExpr(SQLBinaryOpExpr whereExpr, List<CoderCondition> whereColums) {
        if (whereExpr == null) {
            return;
        }
        if (whereExpr.getRight() != null && whereExpr.getRight() instanceof SQLBinaryOpExpr) {
            getWhereExpr((SQLBinaryOpExpr) whereExpr.getRight(), whereColums);
        }
        if (whereExpr.getLeft() != null && whereExpr.getLeft() instanceof SQLBinaryOpExpr) {
            getWhereExpr((SQLBinaryOpExpr) whereExpr.getLeft(), whereColums);
        }
        if (whereExpr.getRight() != null && whereExpr.getRight() instanceof SQLVariantRefExpr) {
            SQLVariantRefExpr sqlVariantRefExpr = (SQLVariantRefExpr) whereExpr.getRight();
            // 属性值为?号，或者以:号开头，则是查询条件
            String proName = sqlVariantRefExpr.getName();
            if (proName != null
                    && ("?".equalsIgnoreCase(proName) || proName.indexOf(":") == 0)
                    && whereExpr.getLeft() instanceof SQLPropertyExpr) {
                SQLPropertyExpr leftPro = (SQLPropertyExpr) whereExpr.getLeft();
                CoderCondition coderCondition = getCoderConditionBySQLPropertyExpr(leftPro);
                if (proName.indexOf(":") == 0) {
                    String javaName = proName.substring(proName.indexOf(":") + 1);
                    coderCondition.setJavaName(javaName);
                }
                whereColums.add(coderCondition);
            }
        }else if (whereExpr.getLeft() != null && whereExpr.getLeft() instanceof SQLPropertyExpr) {
            CoderCondition coderCondition = getCoderConditionBySQLPropertyExpr((SQLPropertyExpr)whereExpr.getLeft());
            whereColums.add(coderCondition);
        }
    }

    private CoderCondition getCoderConditionBySQLPropertyExpr(SQLPropertyExpr leftPro){
        CoderCondition coderCondition = new CoderCondition();
        String jdbcName = leftPro.getName();
        jdbcName = DbUtils.clearSpecialCharacters(jdbcName);
        String javaName = NameConvertUtils.underlineToCamel(jdbcName);
        coderCondition.setJdbcName(jdbcName);
        coderCondition.setJavaName(javaName);
        coderCondition.setJdbcType(Constants.getDefaultJdbcType());
        coderCondition.setJavaType(Constants.getDefaultJavaType());
        return coderCondition;
    }
    /*
     * 通过列名，获取Field  对象
     * @param [columnName]
     * @return
     */
    private Field getDefaultField(String columnName) {
        Field field = new Field();
        columnName = DbUtils.clearSpecialCharacters(columnName);
        field.setName(NameConvertUtils.underlineToCamel(columnName));
        field.setColumn(columnName);
        field.setJdbcType(Constants.getDefaultJdbcType());
        field.setIsPrimary(Constants.FLAG_FALSE);
        field.setJavaType(Constants.getDefaultJavaType());
        return field;
    }


    /**
     * 基于创建语句获取列属性列表
     *
     * @param mySqlCreateTableStatement
     * @return
     */
    public List<Field> getFieldList(MySqlCreateTableStatement mySqlCreateTableStatement) {
        List<Field> fields = new ArrayList<>();
        String priKeyColumn = "";
        List<SQLTableElement> list = mySqlCreateTableStatement.getTableElementList();
        if (list != null && list.size() > 0) {
            for (SQLTableElement tableElement : list) {
                if (tableElement instanceof SQLColumnDefinition) {
                    SQLColumnDefinition sqlColumnDefinition = (SQLColumnDefinition) tableElement;
                    Field field = new Field();
                    String columnName = DbUtils.clearSpecialCharacters(sqlColumnDefinition.getNameAsString());
                    field.setName(getFieldNameByColumn(columnName));
                    if (sqlColumnDefinition.getComment() != null) {
                        field.setRemarks(DbUtils.clearSpecialCharacters(sqlColumnDefinition.getComment().toString()));
                    }
                    field.setColumn(columnName);
                    String jdbcType = "";
                    if (sqlColumnDefinition.getDataType() != null) {
                        jdbcType = DbUtils.clearSpecialCharacters(sqlColumnDefinition.getDataType().getName());
                        if (jdbcType.indexOf("(") > 0) {
                            jdbcType = jdbcType.substring(0, jdbcType.indexOf("("));
                        }
                    }
                    field.setJdbcType(jdbcType);
                    field.setIsPrimary(Constants.FLAG_FALSE);
                    field.setJavaType(getJavaTypeByJdbcType(jdbcType));
                    fields.add(field);
                } else if (tableElement instanceof MySqlPrimaryKey) {
                    MySqlPrimaryKey mySqlPrimaryKey = (MySqlPrimaryKey) tableElement;
                    if (mySqlPrimaryKey.getColumns() != null && mySqlPrimaryKey.getColumns().size() > 0) {
                        SQLSelectOrderByItem sqlSelectOrderByItem = mySqlPrimaryKey.getColumns().get(0);
                        priKeyColumn = sqlSelectOrderByItem.getExpr().toString();
                    }
                }
            }
            if (StringUtils.isNotEmpty(priKeyColumn)) {
                for (Field field : fields) {
                    if (field.getColumn().equalsIgnoreCase(priKeyColumn)) {
                        field.setIsPrimary(Constants.FLAG_TRUE);
                    }
                }
            }
        }
        return fields;
    }

    public String getTableRemarksOfMysql(MySqlCreateTableStatement mySqlCreateTableStatement) {
        if (mySqlCreateTableStatement.getComment() != null) {
            String text = mySqlCreateTableStatement.getComment().toString();
            return DbUtils.clearSpecialCharacters(text);
        }
        return "";
    }

    public String getTableNameOfMysql(MySqlCreateTableStatement mySqlCreateTableStatement) {
        if (mySqlCreateTableStatement.getTableSource().getExpr() != null) {
            String text = mySqlCreateTableStatement.getTableSource().getExpr().toString();
            return DbUtils.clearSpecialCharacters(text);
        }
        return null;
    }


    /**
     * 通过jdbc数据类型获取java类型
     *
     * @param jdbcType
     * @return
     */
    public String getJavaTypeByJdbcType(String jdbcType) {
        // 数据类型准备
        DBType dbType = null;
        DBType[] values = DBType.values();
        for (DBType type : values) {
            if (jdbcType.equalsIgnoreCase(type.toString())) {
                dbType = type;
                break;
            }
        }
        if (dbType == null) {
            log.error("数据库类型不存在。类型为：" + jdbcType + "");
            return null;
        } else {
            return dbType.getJavaType().toString();
        }
    }

    /**
     * 根据数据库列名获取实体字段名字，例如：user_name转换为userName
     *
     * @param code
     * @return
     */
    public String getFieldNameByColumn(String code) {
        if (code.indexOf("_") == -1) {
            return code.toLowerCase();
        }
        String[] strings = code.split("_");
        StringBuffer sb = new StringBuffer();
        sb.append(strings[0].toLowerCase());
        for (int i = 1; i < strings.length; i++) {
            sb.append(strings[i].substring(0, 1).toUpperCase());
            sb.append(strings[i].substring(1).toLowerCase());
        }
        return sb.toString();
    }

}
